﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using WPLDB.DA.Interfaces;
using WPLDB.DA.Models;

namespace WPLDB.SQL
{
    /// <summary>
    /// This class is taken care for executing SQL queries
    /// </summary>
    public sealed class SQLOperations : ISQLOperations
    {
        #region "Variable"

        private string _connectionString;

        #endregion

        #region "ctor"

        /// <summary>
        /// Default constructor with SQL database connection information
        /// </summary>
        /// <param name="connectionString"></param>
        public SQLOperations(string connectionString)
        {
            _connectionString = connectionString;
        }

        #endregion 

        #region "Interface Implementation"

        /// <summary>
        /// This method execute the SQL query and return the result
        /// </summary>
        /// <param name="query">SQL query string</param>
        /// <returns>Output model</returns>
        public IOutputModel<DataSet> ExecuteCommand(string query)
        {

            DataSet ds = new DataSet();
            Exception exception = null;
            IOutputModel<DataSet> model = null;

            try
            {
                SqlConnection sqlConnection = new SqlConnection(_connectionString);
                SqlCommand sqlCommand = new SqlCommand(query);
                sqlCommand.Connection = sqlConnection;
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                sqlConnection.Open();
                sqlDataAdapter.Fill(ds);
                sqlConnection.Close();
            }

            catch (Exception ex) { exception = ex; }

            finally { model = new OutputModel<DataSet> { Error = exception, Result = ds }; }

            return model;
        }

        #endregion
    }
}
